package cn.bopomo.dealer.base.db;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import cn.bopomo.dealer.base.exception.ExceptionCode;
import cn.bopomo.dealer.base.exception.SystemException;
import cn.bopomo.dealer.base.exception.UserException;
import cn.bopomo.dealer.base.util.Page;
import cn.bopomo.dealer.base.util.PageList;



	

public class BaseDAO extends HibernateDaoSupport {

	private Log log = LogFactory.getLog(getClass());
	
	/**
	 * 生成where 语句和排序语句
	 * @param condition   条件字段数组
	 * @param para		  参数数组
	 * @param logic       每个条件的逻辑符号
	 * @param orderBy     根据哪个字段排序
	 * @param isDESC      倒序还是顺序
	 * @return
	 */
	public String generateWhereSQL(Object[] condition,List para,Object[] logic,Object orderBy,boolean isDESC){
		StringBuffer  queryString = new StringBuffer("");
		//看看是否有条件查询，任意一个数组参数为null都为非法
		if (condition!=null && para != null && logic != null){
			//判断条件字段的数目,参数数目,逻辑符号的数目是否一致,目的是检验所传参数是否合理
			if (condition.length == para.size() && condition.length == logic.length){
				queryString.append(" where");
				//循环生成条件语句
				for (int i=0;i < condition.length;i++){
					if (logic[i].equals("in")){
						queryString.append(" ").append(condition[i]).append(" ").append(logic[i]).append(" ").append("(").append(para.get(i).toString()).append(")");
						para.remove(i);
					}else if (logic[i].equals("like")){
						queryString.append(" ").append(condition[i]).append(" ").append(logic[i]).append(" ").append("'%").append(para.get(i).toString()).append("%'");
						para.remove(i);
					}
					else
						queryString.append(" " + condition[i]).append(" " + logic[i] + " ").append("?");
					if (!(i+1>=condition.length)){
						queryString.append(" and");
					}
				}
				//queryString.delete(queryString.lastIndexOf(" and"),queryString.length());
			}
		}
		//判断是否生成排序部分的HQL语句
		if (orderBy!=null)
			//是否倒序
			if (isDESC)
				//根据排序字段参数生成排序部分的HQL语句
				queryString.append(" Order By ").append(orderBy).append(" DESC");
			else
				//根据排序字段参数生成排序部分的HQL语句
				queryString.append(" Order By ").append(orderBy).append(" ASC");
		
		return queryString.toString();
	}
	
	
	public Object sectionFirstObject(String queryString,String whereSQL) throws Exception {
		return this.sectionFirstObject(queryString,whereSQL, null);
	}

	public Object sectionFirstObject(String queryString,String whereSQL, Object paramValue)
			throws Exception {
		if (paramValue == null)
			return this.sectionFirstObject(queryString,whereSQL,new ArrayList());
		else{
			List list = new ArrayList();
			list.add(paramValue);
			return this.sectionFirstObject(queryString,whereSQL, list);
		}
			
	}

	public Object sectionFirstObject(String queryString,String whereSQL, List paramValues)
			throws Exception {
		List ls = this.selectSectionData(queryString,whereSQL, paramValues, 0, 1);
		if (ls != null && ls.size() > 0) {
			return ls.get(0);
		} else {
			return null;
		}
	}
	
	
	/**
	 * 获取相应的数据的Page对象
	 * @param hql       	表对象 e.g "from 表名"
	 * @param condition   	条件字段数组
	 * @param para		  	参数数组
	 * @param logic      	每个条件的逻辑符号
	 * @param orderBy     	根据哪个字段排序
	 * @param isDESC      	倒序还是顺序
	 * @param pageSize	  	第几页数据
	 * @param rowsPerPage	每页显示的数据
	 * @return
	 * @throws Exception 
	 */
	public Page selectSectionData(String hql,Object[] condition,List para,Object[] logic,Object orderBy,boolean isDESC,int pageSize,int rowsPerPage) throws Exception{
		Page page = new Page();
		page.setCurPage(pageSize);
		page.setRowsPerPage(rowsPerPage);
		
		//生成条件HQL语句
		String whereSQL = this.generateWhereSQL(condition,para,logic,orderBy,isDESC);
		

		if (whereSQL ==null)
			whereSQL = "";
			
			
		int totalRows = 0;
		if (page.getMaxRowCount() == 0) {
			String totalHql = "";
			if (hql.indexOf("from") > 0) {
				totalHql = "select count(*) "
						+ hql.substring(hql.indexOf("from"));
			} else {
				totalHql = "select count(*) " + hql;
			}

			if (para != null && para.size() != 0) {
				totalRows = ((Number) this
						.sectionFirstObject(totalHql,whereSQL, para)).intValue();
			} else {
				totalRows = ((Number) this.sectionFirstObject(totalHql,whereSQL))
						.intValue();
			}
			page.setMaxRowCount(totalRows);
		}
		page.initialize();
		
		
		
		//获取数据
		int start = (page.getCurPage() - 1) * page.getRowsPerPage();
		List list = null;
		if (para == null) {
			list = this.selectSectionData(hql,whereSQL,new ArrayList(), start, page.getRowsPerPage());
		} else {
			list = this.selectSectionData(hql,whereSQL,para, start, page.getRowsPerPage());
		}
		page.setList(list);
		return page;
		
	}
	

	/**
	 * 获取部分数据 返回List对象
	 * @param hql			HQL语句 e.g "from 表名"
	 * @param whereSQL		HQL的条件语句部分
	 * @param para			参数数组
	 * @param start			数据开始位置
	 * @param maxrow		获取数据的数量
	 * @return
	 */
	public List selectSectionData(final String hql,final String whereSQL,final List para,final int start,final int maxrow){
		
		final StringBuffer  queryString = new StringBuffer(hql);
		
		if (whereSQL != null)
			queryString.append(whereSQL);
		
		return this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(queryString.toString());
				if (para !=null )
				for (int i = 0; i < para.size(); i++) {
					query.setParameter(i, para.get(i));
				}
				query.setFirstResult(start);
				if (maxrow > 0)
					query.setMaxResults(maxrow);
				return query.list();
			}
		});
	}
	
	public List selectAll(String hql, Object[] params)throws SystemException{
		try{
			List list = null;
			if(params==null || params.length==0)
				list = getHibernateTemplate().find(hql);
			else
				list = getHibernateTemplate().find(hql, params);
			
			return list;
		}catch(Exception e){
			log.warn(e);
			throw new SystemException(ExceptionCode.S_100000, "数据库错误");
		}
	}
	
	public PageList select(final String hql, final Object[] params, final PageList page)throws SystemException{
		try{
			List list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					Query query = session.createQuery(hql.toString());

					for (int i = 0; i < (params!=null?params.length:0); i++) {
						query.setParameter(i, params[i]);
					}
					query.setFirstResult(page.getStartRow());
					query.setMaxResults(page.getPageSize());
					return query.list();
				}
			});
			
			page.setCurPageList(list);
			return page;
		}catch(Exception e){
			log.warn(e);
			throw new SystemException(ExceptionCode.S_100000, "数据库错误");
		}
	}
	
	public int selectTotalCounts(String hql)throws SystemException{
		try{
			List list = getHibernateTemplate().find(hql);
			if(list!=null && list.size()>0)
				return (int)((Long)list.get(0)).longValue();
			else
				return 0;
		}catch(Exception e){
			log.warn("出错", e);
			throw new SystemException(ExceptionCode.S_100000, "数据库错误");
		}
	}
	
	public int selectTotalCounts(String hql, Object[] params)throws SystemException{
		try{
			List list = getHibernateTemplate().find(hql, params);
			if(list!=null && list.size()>0)
				return (int)((Long)list.get(0)).longValue();
			else
				return 0;
		}catch(Exception e){
			log.warn("出错", e);
			throw new SystemException(ExceptionCode.S_100000, "数据库错误");
		}
	}
	
	public Object selectFirstObject(String hql, Object[] params)throws SystemException,UserException{
		Object vo = null;
		List list = null;
		try{
			list = getHibernateTemplate().find(hql, params);
		}catch(Exception e){
			log.warn(e);
		throw new SystemException(ExceptionCode.S_100000,
			"数据操作错误！");
		}
		
//		if(list != null){
//			int size = list.size();
//			if(size>=1){
//				vo = list.get(0);
//			}else if(size==0){
//				log.warn("There are not the record in database");
//				throw new UserException(ExceptionCode.U_210002,
//				"数据库不存在此记录");
//			}
//		}
		if(list != null && list.size()>0){
			vo = list.get(0);
		}
		
		return vo;
	}
	
	public int batchUpdateOrDelete(String hql, Object[] values)throws SystemException{
		try{
			if(values==null || values.length==0)
				return getHibernateTemplate().bulkUpdate(hql);
			else
				return getHibernateTemplate().bulkUpdate(hql, values);
		}catch(Exception e){
			log.warn(e);
			e.printStackTrace();
			throw new SystemException(ExceptionCode.S_100000, "数据库错误");
		}
	}
	
	public void insertOrUpdate(Object vo)throws SystemException{
		try{
			getHibernateTemplate().saveOrUpdate(vo);
		}catch(Exception e){
			log.warn(e);
			throw new SystemException(ExceptionCode.S_100000, "数据库错误");
		}
	}
}
